"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : PACKAGE
Case Name   : 非package所有者系统管理员用户修改package所有者,新所有者没有package
              所属模式下的create权限
Create At   : 2022/04/27
Owner       : @zou_jialiang050
Description :
    1.创建用户1
    2.创建用户2
    3.将用户2的权限赋予用户1
    4.收回用户2在用户1所在模式的create权限
    5.用户1身份声明并定义package
    6.切换至用户1模式,修改package的所属者为用户2
    7.结合系统表gs_package,pg_user查询package所有者
    8.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.返回用户2
    8.成功
History     :
"""

import os
import unittest
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PACKAGE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.user1 = 'u_package_0054_01'
        self.user2 = 'u_package_0054_02'
        self.package = 'p_test_0054'
        self.procedure = 'p_package_0054'
        macro.PASSWD_REPLACE = macro.PASSWD_REPLACE

    def test_package(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:创建用户1 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(f'''
            drop user if exists {self.user1} cascade;
            create user {self.user1} with sysadmin password 
            '{macro.PASSWD_REPLACE}';''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_ROLE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step2:创建用户2 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(f'''
            drop user if exists {self.user2} cascade;
            create user {self.user2} with password '{macro.PASSWD_REPLACE}';
            ''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_ROLE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step3:将用户2的权限赋予用户1 expect:成功'
        self.logger.info(step)
        grant_privi = self.primary_sh.execut_db_sql(
            f'''grant {self.user2} to {self.user1};''')
        self.logger.info(grant_privi)
        self.assertIn('GRANT', grant_privi, '执行失败' + step)

        step = 'step4:收回用户2在用户1所在模式的create权限 expect:成功'
        self.logger.info(step)
        revoke_privi = self.primary_sh.execut_db_sql(
            f'''revoke create on schema {self.user1} from {self.user2};''')
        self.logger.info(revoke_privi)
        self.assertIn('REVOKE', revoke_privi, '执行失败' + step)

        step = 'step5:用户1身份声明并定义package expect:成功'
        self.logger.info(step)
        spec_package = self.primary_sh.execut_db_sql(
            f'''create or replace package {self.package} is
            procedure {self.procedure}();
            end {self.package};

            create or replace package body {self.package} is
            procedure {self.procedure}()
            is
                v_int int;
            begin
                v_int := (select 1+2);
                raise info 'v_int:%',v_int;
            end;
            end {self.package};''',
            sql_type=f" -U {self.user1} -W '{macro.PASSWD_REPLACE}'")
        self.logger.info(spec_package)
        self.assertIn(self.Con.create_package_success_msg, spec_package,
                      '执行失败' + step)
        self.assertIn(self.Con.create_package_body_success_msg, spec_package,
                      '执行失败' + step)

        step = 'step6:切换至用户1模式,修改package的所属者为用户2 expect:成功'
        self.logger.info(step)
        alter_package = self.primary_sh.execut_db_sql(
            f'''set current_schema = {self.user1};
            alter package {self.package} owner to {self.user2};''')
        self.logger.info(alter_package)
        self.assertIn('SET', alter_package, '执行失败' + step)
        self.assertIn('ALTER PACKAGE', alter_package, '执行失败' + step)

        step = 'step7:结合系统表gs_package,pg_user查询package所有者 ' \
               'expect:返回用户2'
        self.logger.info(step)
        select_owner = self.primary_sh.execut_db_sql(
            f'''select usename from pg_user where usesysid = (select pkgowner 
            from gs_package where pkgname = '{self.package}');''')
        self.logger.info(select_owner)
        self.assertIn(self.user2, select_owner, '执行失败' + step)

    def tearDown(self):
        step = 'step8:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_environment = self.primary_sh.execut_db_sql(
            f'''drop package {self.package};''',
            sql_type=f" -U {self.user1} -W {macro.PASSWD_REPLACE}")
        self.logger.info(clean_environment)
        clean_environment1 = self.primary_sh.execut_db_sql(
            f'''drop user {self.user1};
            drop user {self.user2};''')
        self.logger.info(clean_environment1)
        self.assertIn(self.Con.drop_package_success_msg, clean_environment,
                      '执行失败' + step)
        self.assertEqual(clean_environment1.count
                         (self.Con.DROP_ROLE_SUCCESS_MSG), 2,
                         '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
